package com.scuola;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

public class WebService {
	
	public String getServerStatus(){
		return "ONLINE";
	}
	
	public String login(String username, String password, String tipoutente){
		String id = null, ruolo = null;
		try{
			System.out.println("Richiesta Login da "+username);
			Connection conn = Database.getDbConnect();
			if (conn != null){
				String query = "SELECT id, ruolo FROM Utenti WHERE username ='"+username+"' AND password ='"+md5Class.convertToMD5(password)+"'";
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(query);
				
				int element = 0;
				while(rs.next()){
					id = rs.getString("id");
					ruolo = rs.getString("ruolo");
					element++;
				}
				rs.close();
				conn.close();
				
				if (element == 1){
					if(ruolo.equals(tipoutente)){
						System.out.println("Richiesta Login da "+username+" Effettuata correttamente");
						return "OK|"+id+"|"+ruolo;
					}else{
						System.out.println("Richiesta Login da "+username+" Fallita (Ruolo e TipoUtente incoerenti)");
						return "NO";
					}
				}else{
					System.out.println("Richiesta Login da "+username+" Fallita (Nessun utente trovato)");
					return "NO";
				}
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println("Richiesta Login da "+username+" Fallita (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}
	
	public String getDataById(String id){
		try{
			System.out.println("Richiesta informazioni utente " +id);
			Connection conn = Database.getDbConnect();
			if (conn != null){
				String query = "SELECT * FROM Utenti WHERE id ='"+id+"'";
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(query);
				JSONObject obj = new JSONObject();
				JSONArray list = new JSONArray();
				
				while(rs.next()){
					obj.put("nome", rs.getObject("nome"));
					obj.put("cognome", rs.getObject("cognome"));
					obj.put("sesso", rs.getObject("sesso"));
					obj.put("classe", rs.getObject("classe"));
					list.add(obj.toJSONString());
				}
				
				rs.close();
				conn.close();
				return list.toJSONString();
			}
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Errore getDataById (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}
	
	public String getClassByProfId(String id){
		try{
			System.out.println("Richiesta classi per utente " +id);
			Connection conn = Database.getDbConnect();
			if (conn != null){
				String query = "SELECT DISTINCT CL.classe, CL.id_classe FROM Classe CL INNER JOIN Cattedra CA ON CA.id_classe = CL.id_classe WHERE CA.id_docente ='"+id+"'";
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(query);
				JSONObject obj = new JSONObject();
				JSONArray list = new JSONArray();
				
				while(rs.next()){
					obj.put("idclasse", rs.getObject("id_classe"));
					obj.put("classe", rs.getObject("classe"));
					list.add(obj.toJSONString());
				}
				
				rs.close();
				conn.close();
				return list.toJSONString();
			}
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Errore getDataById (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}
	
	public String getAlumniByClassId(String classId){
		try{
			System.out.println("Richiesta Alunni della classe " +classId);
			Connection conn = Database.getDbConnect();
			if (conn != null){
				String query = "SELECT cognome, nome, id FROM Utenti WHERE classe = '"+classId+"' ORDER BY cognome";
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(query);
				JSONObject obj = new JSONObject();
				JSONArray list = new JSONArray();
				
				while(rs.next()){
					obj.put("cognome", rs.getObject("cognome"));
					obj.put("nome", rs.getObject("nome"));
					obj.put("id", rs.getObject("id"));
					list.add(obj.toJSONString());
				}
				
				rs.close();
				conn.close();
				return list.toJSONString();
			}
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Errore getAlumniByClassId (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}
	
	public String getMaterieByDocenteId(String docenteId, String classId){
		try{
			System.out.println("Richiesta Materie del docente "+docenteId+" che insegna nella classe "+classId);
			Connection conn = Database.getDbConnect();
			if (conn != null){
				String query = "SELECT MA.id_materia, MA.descrizione FROM Materie MA INNER JOIN Cattedra CA ON MA.id_materia = CA.id_materia WHERE CA.id_docente = '"+docenteId+"' AND CA.id_classe = '"+classId+"' ORDER BY MA.descrizione";
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(query);
				JSONObject obj = new JSONObject();
				JSONArray list = new JSONArray();
				
				while(rs.next()){
					obj.put("id_materia", rs.getObject("id_materia"));
					obj.put("descrizione", rs.getObject("descrizione"));
					list.add(obj.toJSONString());
				}
				
				rs.close();
				conn.close();
				return list.toJSONString();
			}
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Errore getMaterieByDocenteId (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}
	
	public String getAssenzeByClassAndDate(String giorno, String idclasse){
		try{
			System.out.println("Richiesta azzenze per la classe "+idclasse);
			Connection conn = Database.getDbConnect();
			if (conn!= null){
				String query = "SELECT A.id_studente FROM Assenze A WHERE A.data = '"+giorno+"' AND A.id_classe = "+idclasse;
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(query);		
				JSONObject obj = new JSONObject();
				JSONArray list = new JSONArray();
				
				while(rs.next()){
					obj.put("idstudente", rs.getObject("A.id_studente"));
					list.add(obj.toJSONString());
				}
				
				rs.close();
				conn.close();
				return list.toJSONString();
			}
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Errore getAssenzeByClassAndDate (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}
	
	public String setAssenti(String insert, String delete, String idclasse){
		try{
			System.out.println("Salvataggio assenze per la classe "+idclasse);
			Connection conn = Database.getDbConnect();
			if (conn!= null){
				DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
				Date date = new Date();
				String data = dateFormat.format(date).toString();
				
				if(insert.length() != 0){
					String[] insertID = insert.split("\\|");
					for(int i = 0; i < insertID.length; i++){
						String query = "INSERT INTO Assenze (id_studente, id_classe, data) VALUES ("+insertID[i]+", "+idclasse+", '"+data+"'); ";
						Statement stmt = conn.createStatement();
						stmt.execute(query);		
					}
				}
				if(delete.length() != 0){
					String[] deleteID = delete.split("\\|");
					for(int i = 0; i < deleteID.length; i++){
						String query = "DELETE FROM Assenze WHERE id_studente = "+deleteID[i]+" AND data = '"+data+"'; ";
						Statement stmt = conn.createStatement();
						stmt.execute(query);
					}
				}
				
				conn.close();
				return "OK";
			}
		}catch(Exception e){
			e.printStackTrace();
			return "ERROR";
		}
		return "ERROR";
	}
	
	public String setAvviso(String classId, String mittenteId, String destId, String messaggio){
		try{
			System.out.println("Salvataggio avviso del docente "+mittenteId);
			Connection conn = Database.getDbConnect();
			if (conn!= null){
				DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
				Date date = new Date();
				System.out.println();
				String query = "INSERT INTO Avvisi (id_classe, id_mittente, id_destinatario, descrizione, data) VALUES ("+classId+", "+mittenteId+", "+destId+", '"+messaggio+"', '"+dateFormat.format(date)+"'); ";
				Statement stmt = conn.createStatement();
				stmt.execute(query);		
				conn.close();
				return "OK";
			}
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Errore getAssenzeByClassAndDate (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}
	
	public String getAvviso(String classId, String docenteId){
		try{
			System.out.println("Richiesta avviso del docente "+docenteId);
			Connection conn = Database.getDbConnect();
			if (conn!= null){
				String query = "SELECT U.cognome, U.nome, A.id_mittente, A.data, A.descrizione FROM Avvisi A INNER JOIN Utenti U ON U.id = A.id_mittente WHERE A.id_destinatario = "+docenteId+" AND A.id_classe = "+classId+" ORDER BY A.data;";
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(query);
				JSONObject obj = new JSONObject();
				JSONArray list = new JSONArray();
				
				while(rs.next()){
					obj.put("cognome", rs.getObject("U.cognome"));
					obj.put("nome", rs.getObject("U.nome"));
					obj.put("id_mittente", rs.getObject("A.id_mittente"));
					obj.put("data", rs.getObject("A.data").toString());
					obj.put("descrizione", rs.getObject("A.descrizione"));
					list.add(obj.toJSONString());
				}
				rs.close();
				conn.close();
				return list.toJSONString();
			}
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Errore getAvviso (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}
	
	public String getVoto(String studenteId, String materiaId){
		try{
			System.out.println("Richiesta voto dello studente "+studenteId);
			Connection conn = Database.getDbConnect();
			if (conn!= null){
				String query = "SELECT * FROM Voti WHERE id_studente = "+studenteId+" AND id_materia = "+materiaId;
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(query);
				JSONObject obj = new JSONObject();
				JSONArray list = new JSONArray();
				
				while(rs.next()){
					obj.put("classeId", rs.getObject("id_classe"));
					obj.put("materiaId", rs.getObject("id_materia"));
					obj.put("studenteId", rs.getObject("id_studente"));
					obj.put("docenteId", rs.getObject("id_docente"));
					obj.put("data", rs.getObject("data").toString());
					obj.put("voto", rs.getObject("voto"));
					obj.put("tipologia", rs.getObject("tipologia"));
					obj.put("idVoto", rs.getObject("id"));
					list.add(obj.toJSONString());
				}
				rs.close();
				conn.close();
				return list.toJSONString();
			}
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Errore getVoto (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}
	
	public String setVoto(String classId, String materiaId, String studenteId, String docenteId, String nuovoVoto, String voto, String data, String tipologia, String votoId){
		try{
			System.out.println("Salvataggio voto dello studente "+studenteId);
			Connection conn = Database.getDbConnect();
			if (conn!= null){
				String query = null;
				if(nuovoVoto.equals("true")){
					query = "INSERT INTO Voti (id_classe, id_materia, id_studente, id_docente, data, voto, tipologia) VALUES ("+classId+", "+materiaId+", "+studenteId+", "+docenteId+", '"+data+"', "+voto+", '"+tipologia+"');";
				}else{
					query = "UPDATE Voti SET voto = "+voto+", data = '"+data+"', tipologia = '"+tipologia+"' WHERE id = "+votoId;
				}
				Statement stmt = conn.createStatement();
				stmt.execute(query);		
				conn.close();
				return "OK";
			}
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Errore setVoto (Exception)");
			return "ERRORE";
		}
		return "ERRORE";
	}

}
